﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using System;
using System.Drawing;
using System.Text.RegularExpressions;
using NPOI.SS.Util;
using NPOI.HSSF.UserModel;
using System.Text.RegularExpressions;
using VOL.Entity.DomainModels;
using VOL.Core.Extensions;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Information;
using NPOI.OpenXmlFormats.Wordprocessing;
using VOL.Entity.VM;
using System.Xml.Linq;
using System.Collections;

namespace VOL.System.Services
{
    public class NpoiService
    {

        public NpoiService()
        {

        }
        public string GetWwwRootDirectory()
        {
            string baseDirectory = AppDomain.CurrentDomain.BaseDirectory;
            string wwwRootDirectory = Path.Combine(baseDirectory, "wwwroot");
            return wwwRootDirectory;
        }

        #region 读文件的值
        public void ReadExcelForMachingCenterFileNew(string path, upper_ftpmachiningcenter entity, List<upper_ftpprogram> upperftpprograms, PLAN_WORK_ORDER pLAN_WORK_ORDER, Stream fs = null)
        {
            // 读取文件
            if (fs == null)
            {
                fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            }

            using (fs)
            {/*If you open the file with the extension is .xls, use HSSFWorkbook, and if .xlsx, use XSSFWorkbook. Hope it solves your problem.*/
                IWorkbook workbook;
                if (path.Contains(".xlsx"))
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (path.Contains(".xls"))
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    return;
                }
                ISheet sheet = workbook.GetSheetAt(0);
                // 记录已处理的合并单元格
                HashSet<CellRangeAddress> processedMergeCells = new HashSet<CellRangeAddress>();
                // 遍历行和单元格，读取数据
                for (int row = 0; row <= sheet.LastRowNum; row++)
                {
                    IRow currentRow = sheet.GetRow(row);
                    if (currentRow != null)
                    {
                        for (int cell = 0; cell < currentRow.LastCellNum; cell++)
                        {
                            if (cell == 29)
                            {
                                ICell currentCellNew = currentRow.GetCell(cell);
                            }
                            ICell currentCell = currentRow.GetCell(cell);
                            if (currentCell != null)
                            {
                                // 处理合并单元格
                                if (IsMergedCell(currentCell))
                                {
                                    CellRangeAddress mergedRegion = GetMergedRegion(sheet, currentCell);
                                    if (mergedRegion != null && !processedMergeCells.Contains(mergedRegion))
                                    {
                                        string cellValue = GetMergedCellValue(sheet, mergedRegion);
                                        // 处理合并单元格的值
                                        //Console.WriteLine("Merged Cell: {0}", cellValue);
                                        //根据当前单元格信息和值，对实体进行赋值
                                        //如果是注意事项 说明结束了  
                                        if (cellValue == "注意事项")
                                        {
                                            break;
                                        }
                                        if (currentCell.ColumnIndex == 29 && currentCell.CellType == CellType.Numeric)
                                        {
                                            string atime = currentCell.DateCellValue.TimeOfDay.ToString();
                                            // 如果需要检查时间的有效性，可以使用以下代码
                                            TimeSpan timeValue;
                                            bool isValidTime = TimeSpan.TryParse(atime, out timeValue);
                                            // bool isZeroTimeAndValid = isValidTime && timeValue.TotalSeconds == 0;
                                            if (isValidTime)
                                            {
                                                cellValue = currentCell.DateCellValue.ToString("hh:mm:ss");
                                            }

                                        }
                                      
                                        
                                        if (currentCell.Address.ToString() == "AH3" || currentCell.Address.ToString() == "AH4"
                                            || currentCell.Address.ToString() == "AH5" || currentCell.Address.ToString() == "AL3"
                                            || currentCell.Address.ToString() == "AL4" || currentCell.Address.ToString() == "AL5"
                                            || currentCell.Address.ToString() == "AP3" || currentCell.Address.ToString() == "AP4"
                                            || currentCell.Address.ToString() == "AP5")
                                        {
                                            SetEntityEx(currentCell, entity, ref pLAN_WORK_ORDER, ref upperftpprograms, cellValue, cellValue);//附加值

                                        }
                                        else
                                        {
                                            if (currentCell.Address.ToString() == "AB")
                                            {
                                                string atime = currentCell.DateCellValue.TimeOfDay.ToString();
                                                // 如果需要检查时间的有效性，可以使用以下代码
                                                TimeSpan timeValue;
                                                bool isValidTime = TimeSpan.TryParse(atime, out timeValue);
                                                // bool isZeroTimeAndValid = isValidTime && timeValue.TotalSeconds == 0;
                                                if (isValidTime)
                                                {
                                                    cellValue = currentCell.DateCellValue.ToString("hh:mm:ss");
                                                }
                                            }
                                                SetEntityNew(currentCell, entity, ref upperftpprograms, cellValue);
                                        }
                                        // upperftpprograms = ftpprograms;
                                        // 将合并单元格添加到已处理集合
                                        processedMergeCells.Add(mergedRegion);
                                    }
                                }
                                // 处理普通单元格
                                else
                                {
                                    if (currentCell.Address.ToString() == "AE3")//版本号
                                    {
                                        pLAN_WORK_ORDER.Version = currentCell.ToString(); 
                                    }
                                    if (currentCell.Address.ToString() == "AE4")//硬度
                                    {
                                        
                                        pLAN_WORK_ORDER.TollHardness = decimal.Parse(string.IsNullOrEmpty(currentCell.ToString()) ? "0" : currentCell.ToString());
                                    }
                                    if (currentCell.Address.ToString() == "AE5")//硬度
                                    {
                                        pLAN_WORK_ORDER.MaterialHardness = decimal.Parse(string.IsNullOrEmpty(currentCell.ToString())?"0": currentCell.ToString());
                                    }
                                    if (currentCell.Address.ToString() == "AE7")//刀柄
                                    {
                                        pLAN_WORK_ORDER.HolderType = currentCell.ToString();
                                    }
                                    if (currentCell.Address.ToString() == "AE8")//转速
                                    {
                                        pLAN_WORK_ORDER.RotationalSpeed = decimal.Parse(string.IsNullOrEmpty(currentCell.ToString()) ? "0" : currentCell.ToString());
                                    }

                                    if (currentCell.RowIndex > 17)
                                    {
                                        string cellValue = currentCell.ToString();
                                        string pattern = @"^([A-Z]{1,2})(\d+)$";
                                        Match match = Regex.Match(currentCell.Address.ToString(), pattern);
                                        string firstLetter = "";
                                        if (match.Success)
                                        {
                                            firstLetter = match.Groups[1].Value; // 获取第一个字母的值
                                        }
                                        switch (firstLetter)
                                        {
                                            case "F":
                                                upperftpprograms.LastOrDefault().tool_collet = cellValue;
                                                break;

                                            case "G":
                                                upperftpprograms.LastOrDefault().tool_handle = cellValue;
                                                upperftpprograms.LastOrDefault().Holder_Code = cellValue;
                                                break;
                                            case "AD":
                                                upperftpprograms.LastOrDefault().knife_width = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                            case "AE":
                                                upperftpprograms.LastOrDefault().location = string.IsNullOrWhiteSpace(cellValue) ? 0 : int.Parse(cellValue);
                                                break;
                                            case "AF":
                                                upperftpprograms.LastOrDefault().accuracy = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                            case "AG":
                                                upperftpprograms.LastOrDefault().vendor_code = cellValue;
                                                break;
                                            case "AH":
                                                upperftpprograms.LastOrDefault().tool_materials = cellValue;
                                                break;
                                            case "AI":
                                                upperftpprograms.LastOrDefault().Ace1LTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);

                                                break;
                                            case "AJ":
                                                upperftpprograms.LastOrDefault().Ace1DTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                            case "AK":
                                                upperftpprograms.LastOrDefault().Ace2LTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                            case "AL":
                                                upperftpprograms.LastOrDefault().Ace2DTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                            case "AM":
                                                upperftpprograms.LastOrDefault().Ace3LTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                            case "AN":
                                                upperftpprograms.LastOrDefault().Ace3DTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                        }
                                    }


                                    // 处理普通单元格的值
                                    //Console.WriteLine("Cell ({0},{1}): {2}", row, cell, cellValue);
                                    //根据当前单元格信息和值，对实体进行赋值
                                }
                            }
                        }
                    }
                }

                //图片
                IList pictures = sheet.Workbook.GetAllPictures();
                foreach (NPOI.XSSF.UserModel.XSSFPictureData pic in pictures)
                { string ext = pic.SuggestFileExtension();//获取扩展名
                    string path1 = string.Empty;
                    string savepath = GetWwwRootDirectory() + "\\Upload\\Tables\\Sys_Dictionary\\Img"; //@"C:\gitmes\Net6开发版\VOL.WebApi\wwwroot\Upload\Tables\Sys_Dictionary\Img";//"C:\\FTP_Remote_Path\\Img";
                    string imgName = DateTime.Now.ToString("yyyyMMddHHmmss");
                    if (ext.Equals("jpeg"))
                    {
                       
                        Image png = Image.FromStream(new MemoryStream(pic.Data));
                        path1 = Path.Combine(savepath, string.Format("{0}.jpg", entity.mold_number));
                        pLAN_WORK_ORDER.Img = @"/Upload/Tables/Sys_Dictionary/Img/" + entity.mold_number + ".jpg"; ;// GetWwwRootDirectory() + "\\Upload\\Tables\\Sys_Dictionary\\Img\\" + entity.workpiece_name + ".jpg";
                        if (File.Exists(path1))
                        {
                            png.Save(path1);
                        }
                        else
                        {
                            if (File.Exists(savepath))
                            {

                            }
                            else
                            {
                                Directory.CreateDirectory(savepath);
                            }

                            png.Save(path1);
                        }

                    }
                    if (ext.Equals("png"))
                    {
                        Image png = Image.FromStream(new MemoryStream(pic.Data));
                        path1 = Path.Combine(savepath, string.Format("{0}.jpg", entity.mold_number));
                        pLAN_WORK_ORDER.Img = @"/Upload/Tables/Sys_Dictionary/Img/" + entity.mold_number + ".jpg"; ;// GetWwwRootDirectory() + "\\Upload\\Tables\\Sys_Dictionary\\Img\\" + entity.workpiece_name + ".jpg";
                        if (File.Exists(path1))
                        {
                            png.Save(path1);
                        }
                        else
                        {
                            if (File.Exists(savepath))
                            {

                            }
                            else
                            {
                                Directory.CreateDirectory(savepath);
                            }
                          
                            png.Save(path1);
                        }

                    }
                }
               

            }


        }
        public void ReadExcelForMachingCenterFile(string path, upper_ftpmachiningcenter entity, List<upper_ftpprogram> upperftpprograms, PLAN_WORK_ORDER pLAN_WORK_ORDER, Stream fs = null)
        {
            // 读取文件
            if (fs == null)
            {
                fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            }

            using (fs)
            {/*If you open the file with the extension is .xls, use HSSFWorkbook, and if .xlsx, use XSSFWorkbook. Hope it solves your problem.*/
                IWorkbook workbook;
                if (path.Contains(".xlsx"))
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (path.Contains(".xls"))
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    return;
                }
                ISheet sheet = workbook.GetSheetAt(0);
                // 记录已处理的合并单元格
                HashSet<CellRangeAddress> processedMergeCells = new HashSet<CellRangeAddress>();
                // 遍历行和单元格，读取数据
                for (int row = 0; row <= sheet.LastRowNum; row++)
                {
                    IRow currentRow = sheet.GetRow(row);
                    if (currentRow != null)
                    {
                        for (int cell = 0; cell < currentRow.LastCellNum; cell++)
                        {
                            if (cell == 29)
                            {
                                ICell currentCellNew = currentRow.GetCell(cell);
                            }
                            ICell currentCell = currentRow.GetCell(cell);
                            if (currentCell != null)
                            {
                                // 处理合并单元格
                                if (IsMergedCell(currentCell))
                                {
                                    CellRangeAddress mergedRegion = GetMergedRegion(sheet, currentCell);
                                    if (mergedRegion != null && !processedMergeCells.Contains(mergedRegion))
                                    {
                                        string cellValue = GetMergedCellValue(sheet, mergedRegion);
                                        // 处理合并单元格的值
                                        //Console.WriteLine("Merged Cell: {0}", cellValue);
                                        //根据当前单元格信息和值，对实体进行赋值
                                        //如果是注意事项 说明结束了  
                                        if (cellValue == "注意事项")
                                        {
                                            break;
                                        }
                                        if (currentCell.ColumnIndex == 29 && currentCell.CellType == CellType.Numeric)
                                        {
                                            string atime = currentCell.DateCellValue.TimeOfDay.ToString();
                                            // 如果需要检查时间的有效性，可以使用以下代码
                                            TimeSpan timeValue;
                                            bool isValidTime = TimeSpan.TryParse(atime, out timeValue);
                                            // bool isZeroTimeAndValid = isValidTime && timeValue.TotalSeconds == 0;
                                            if (isValidTime)
                                            {
                                                cellValue = currentCell.DateCellValue.ToString("hh:mm:ss");
                                            }

                                        }
                                        if (currentCell.Address.ToString() == "AH3" || currentCell.Address.ToString() == "AH4"
                                            || currentCell.Address.ToString() == "AH5" || currentCell.Address.ToString() == "AL3"
                                            || currentCell.Address.ToString() == "AL4" || currentCell.Address.ToString() == "AL5"
                                            || currentCell.Address.ToString() == "AP3")
                                        {
                                            SetEntityEx(currentCell, entity, ref pLAN_WORK_ORDER, ref upperftpprograms, cellValue, cellValue);//附加值

                                        }
                                        else
                                        {
                                            SetEntity(currentCell, entity, ref upperftpprograms, cellValue);
                                        }
                                        // upperftpprograms = ftpprograms;
                                        // 将合并单元格添加到已处理集合
                                        processedMergeCells.Add(mergedRegion);
                                    }
                                }
                                // 处理普通单元格
                                else
                                {
                                    if (currentCell.RowIndex > 17)
                                    {
                                        string cellValue = currentCell.ToString();
                                        string pattern = @"^([A-Z]{1,2})(\d+)$";
                                        Match match = Regex.Match(currentCell.Address.ToString(), pattern);
                                        string firstLetter = "";
                                        if (match.Success)
                                        {
                                            firstLetter = match.Groups[1].Value; // 获取第一个字母的值
                                        }
                                        switch (firstLetter)
                                        {
                                            case "F":
                                                upperftpprograms.LastOrDefault().tool_collet = cellValue;
                                                break;

                                            case "G":
                                                upperftpprograms.LastOrDefault().tool_handle = cellValue;
                                                upperftpprograms.LastOrDefault().Holder_Code = cellValue;
                                                break;
                                            case "AF":
                                                upperftpprograms.LastOrDefault().accuracy = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                            case "AG":
                                                upperftpprograms.LastOrDefault().vendor_code = cellValue;
                                                break;
                                            case "AH":
                                                upperftpprograms.LastOrDefault().tool_materials = cellValue;
                                                break;
                                            case "AI":
                                                upperftpprograms.LastOrDefault().Ace1LTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);

                                                break;
                                            case "AJ":
                                                upperftpprograms.LastOrDefault().Ace1DTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                            case "AK":
                                                upperftpprograms.LastOrDefault().Ace2LTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                            case "AL":
                                                upperftpprograms.LastOrDefault().Ace2DTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                            case "AM":
                                                upperftpprograms.LastOrDefault().Ace3LTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                            case "AN":
                                                upperftpprograms.LastOrDefault().Ace3DTheory = string.IsNullOrWhiteSpace(cellValue) ? 0 : decimal.Parse(cellValue);
                                                break;
                                        }
                                    }


                                    // 处理普通单元格的值
                                    //Console.WriteLine("Cell ({0},{1}): {2}", row, cell, cellValue);
                                    //根据当前单元格信息和值，对实体进行赋值
                                }
                            }
                        }
                    }
                }
            }


        }


        // 获取合并单元格的值
        // 判断单元格是否为合并单元格
        private static bool IsMergedCell(ICell cell)
        {
            return cell.IsMergedCell;
        }

        // 获取合并单元格的值
        private static string GetMergedCellValue(ISheet sheet, CellRangeAddress mergedRegion)
        {
            int firstRow = mergedRegion.FirstRow;
            int firstColumn = mergedRegion.FirstColumn;
            IRow mergedRow = sheet.GetRow(firstRow);
            ICell mergedCell = mergedRow.GetCell(firstColumn);
            return mergedCell.ToString();
        }

        // 获取单元格所属的合并单元格区域
        private static CellRangeAddress GetMergedRegion(ISheet sheet, ICell cell)
        {
            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress mergedRegion = sheet.GetMergedRegion(i);
                if (mergedRegion.IsInRange(cell.RowIndex, cell.ColumnIndex))
                {
                    return mergedRegion;
                }
            }
            return null;
        }
        /// <summary>
        /// 根据单元格信息对实体进行赋值 附加值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="entity"></param>
        /// <param name="pLAN_WORK_ORDER"></param>
        /// <param name="upper_ftpprograms"></param>
        /// <param name="value"></param>
        /// <param name="valueEx"></param>
        private void SetEntityEx(ICell cell, upper_ftpmachiningcenter entity, ref PLAN_WORK_ORDER pLAN_WORK_ORDER, ref List<upper_ftpprogram> upper_ftpprograms, string value, string valueEx)
        {
            if (0 <= cell.RowIndex && cell.RowIndex < 16)
            {
                switch (cell.Address.ToString())
                {
                    case "AP3":
                        pLAN_WORK_ORDER.ProcessWeight = decimal.Parse(value);
                        break;
                    case "AP4":
                        pLAN_WORK_ORDER.ProcessingMaterial = value;//加工材料
                        break;
                    case "AP5":
                        pLAN_WORK_ORDER.process_precision = value;//加工精度
                        break;
                    case "AL3":
                        if(string.IsNullOrEmpty(value))
                        {
                            pLAN_WORK_ORDER.PlanStart = DateTime.Now;
                        }
                        else
                        {
      
                            pLAN_WORK_ORDER.PlanStart = DateTime.Parse(cell.DateCellValue.ToString());
                        }
                     
                        break;
                    case "AL4":
                        if(string.IsNullOrEmpty(value))
                        { 
                            pLAN_WORK_ORDER.PlanEnd = DateTime.Now;}
                        else
                        {
                            pLAN_WORK_ORDER.PlanEnd = DateTime.Parse(cell.DateCellValue.ToString());
                        }
                        
                        break;
                    case "AL5":
                        pLAN_WORK_ORDER.process_type = value;
                        break;

                    case "AH3":
                        pLAN_WORK_ORDER.measure_number = value;
                        break;
                    case "AH4":
                        pLAN_WORK_ORDER.centring_number = value;
                        break;
                    case "AH5":
                        pLAN_WORK_ORDER.make_level_number = value;
                        break;
                


                }
            }
            SetEntity(cell, entity, ref upper_ftpprograms, value);
        }
        /// <summary>
        /// 根据单元格信息对实体进行赋值
        /// </summary>
        /// <param name="path"></param>
        private void SetEntityNew(ICell cell, upper_ftpmachiningcenter entity, ref List<upper_ftpprogram> upper_ftpprograms, string value)
        {
            if (0 <= cell.RowIndex && cell.RowIndex < 16)
            {
                switch (cell.Address.ToString())
                {
                    case "Z3":
                        entity.mold_number = value;
                        break;
                    case "X3":
                        entity.mold_number = value;
                        break;
                    case "Z4":
                        entity.workpiece_name = value;
                        break;
                    case "X4":
                        entity.workpiece_name = value;
                        break;
                    case "Z5":
                        entity.workpiece_size = value;
                        break;
                    case "X5":
                        entity.workpiece_size = value;
                        break;
                    case "Z7":
                        entity.x_touches = value;
                        break;
                    case "X7":
                        entity.x_touches = value;
                        break;
                    case "AC7":
                        entity.y_touches = value;
                        break;
                    case "AA7":
                        entity.y_touches = value;
                        break;
                    case "Z8":
                        entity.z_touches = value;
                        break;
                    case "X8":
                        entity.z_touches = value;
                        break;
                    case "Z9":
                        entity.clamping_method = value;
                        break;
                    case "X9":
                        entity.clamping_method = value;
                        break;
                    case "Z10":
                        entity.coordinate_name = value;
                        break;
                    case "X10":
                        entity.coordinate_name = value;
                        break;
                    case "Z11":
                        entity.z_max = value;
                        break;
                    case "X11":
                        entity.z_max = value;
                        break;
                    case "Z12":
                        entity.z_min = value;
                        break;
                    case "X12":
                        entity.z_min = value;
                        break;
                    case "Z14":
                        entity.processing_machines = value;
                        break;
                    case "X14":
                        entity.processing_machines = value;
                        break;
                    case "Z15":
                        entity.program = value;
                        break;
                    case "X15":
                        entity.program = value;
                        break;
                    case "Z16":
                        entity.processing_time = value;
                        break;
                    case "X16":
                        entity.processing_time = value;
                        break;
                    case "AE3":
                        entity.processing_time = value;
                        break;
                }
            }
            else if (cell.RowIndex == 16 || cell.RowIndex == 17)
            {

            }
            else
            {
                string pattern = @"^([A-Z]{1,2})(\d+)$"; // 正则表达式模式，使用捕获组
                Match match = Regex.Match(cell.Address.ToString(), pattern);
                if (match.Success)
                {
                    string firstLetter = match.Groups[1].Value; // 获取第一个字母的值
                    string digits = match.Groups[2].Value; // 获取数字的值

                    if (firstLetter == "A")
                    {
                        upper_ftpprograms.Add(new upper_ftpprogram() { sequence = value });
                    }
                    else
                    {
                        switch (firstLetter)
                        {
                            case "C":
                                upper_ftpprograms.LastOrDefault().program_name = value;
                                break;
                            //case "F":
                            //    upper_ftpprograms.LastOrDefault().tool_collet = value;
                            //    break;

                            //case "G":
                            //    upper_ftpprograms.LastOrDefault().tool_handle = value;
                            //    break;

                            //case "H":

                            //    upper_ftpprograms.LastOrDefault().tool_name = value;
                            //    break;
                            //case "K":
                            //    upper_ftpprograms.LastOrDefault().knife_length = decimal.TryParse(value, out var aa) ? aa : 0;
                            //    break;
                            //case "M":
                            //    upper_ftpprograms.LastOrDefault().blade_length = decimal.TryParse(value, out var bb) ? bb : 0;
                            //    break;
                            //case "O":
                            //    upper_ftpprograms.LastOrDefault().category = value;
                            //    if (value == "光")
                            //    {
                            //        upper_ftpprograms.LastOrDefault().accuracy = 0.03m;
                            //    }
                            //    else
                            //    {
                            //        upper_ftpprograms.LastOrDefault().accuracy = 0.1m;
                            //    }
                            //    break;
                            //case "Q":
                            //    upper_ftpprograms.LastOrDefault().rotate_speed = decimal.TryParse(value, out var cc) ? cc : 0;
                            //    break;
                            //case "S":
                            //    upper_ftpprograms.LastOrDefault().feed = value;
                            //    break;
                            //case "U":
                            //    upper_ftpprograms.LastOrDefault().side_bottom_margin = value;
                            //    break;
                            //case "W":
                            //    upper_ftpprograms.LastOrDefault().z_depth = decimal.TryParse(value, out var dd) ? dd : 0;
                            //    break;
                            //case "Y":
                            //    upper_ftpprograms.LastOrDefault().remark = value;
                            //    break;
                            //case "AD":
                            //    upper_ftpprograms.LastOrDefault().time = value;
                            //    break;

                            //新版
                            case "F":

                                upper_ftpprograms.LastOrDefault().tool_name = value;
                                break;
                            case "I":
                                upper_ftpprograms.LastOrDefault().knife_length = decimal.TryParse(value, out var aa) ? aa : 0;
                                break;
                            case "K":
                                upper_ftpprograms.LastOrDefault().blade_length = decimal.TryParse(value, out var bb) ? bb : 0;
                                break;
                            case "M":
                                upper_ftpprograms.LastOrDefault().category = value;
                                if (value == "光")
                                {
                                    upper_ftpprograms.LastOrDefault().accuracy = 0.03m;
                                }
                                else
                                {
                                    upper_ftpprograms.LastOrDefault().accuracy = 0.1m;
                                }
                                break;
                            case "O":
                                upper_ftpprograms.LastOrDefault().rotate_speed = decimal.TryParse(value, out var cc) ? cc : 0;
                                break;
                            case "Q":
                                upper_ftpprograms.LastOrDefault().feed = value;
                                break;
                            case "S":
                                upper_ftpprograms.LastOrDefault().side_bottom_margin = value;
                                break;
                            case "U":
                                upper_ftpprograms.LastOrDefault().z_depth = decimal.TryParse(value, out var dd) ? dd : 0;
                                break;
                            case "W":
                                upper_ftpprograms.LastOrDefault().tool_handle = value;
                               // upper_ftpprograms.LastOrDefault().remark = value;
                                break;
                            case "AB":
                             //   upper_ftpprograms.LastOrDefault().time = value;
                                string atime = cell.DateCellValue.TimeOfDay.ToString();
                                // 如果需要检查时间的有效性，可以使用以下代码
                                TimeSpan timeValue;
                                bool isValidTime = TimeSpan.TryParse(atime, out timeValue);
                                // bool isZeroTimeAndValid = isValidTime && timeValue.TotalSeconds == 0;
                                if (isValidTime)
                                {
                                    upper_ftpprograms.LastOrDefault().time = cell.DateCellValue.ToString("hh:mm:ss");
                                }

                                break;
                            //case "AD":
                            //    //    //upper_ftpprograms.LastOrDefault().tool_collet = value;
                         
                            //    break;

                                //case "AE":
                                //    upper_ftpprograms.LastOrDefault().tool_handle = value;
                                //    break;
                        }
                    }

                }



            }

        }
        private void SetEntity(ICell cell, upper_ftpmachiningcenter entity, ref List<upper_ftpprogram> upper_ftpprograms, string value)
        {
            if (0 <= cell.RowIndex && cell.RowIndex < 16)
            {
                switch (cell.Address.ToString())
                {
                    case "Z3":
                        entity.mold_number = value;
                        break;
                    case "X3":
                        entity.mold_number = value;
                        break;
                    case "Z4":
                        entity.workpiece_name = value;
                        break;
                    case "X4":
                        entity.workpiece_name = value;
                        break;
                    case "Z5":
                        entity.workpiece_size = value;
                        break;
                    case "X5":
                        entity.workpiece_size = value;
                        break;
                    case "Z7":
                        entity.x_touches = value;
                        break;
                    case "X7":
                        entity.x_touches = value;
                        break;
                    case "AC7":
                        entity.y_touches = value;
                        break;
                    case "AA7":
                        entity.y_touches = value;
                        break;
                    case "Z8":
                        entity.z_touches = value;
                        break;
                    case "X8":
                        entity.z_touches = value;
                        break;
                    case "Z9":
                        entity.clamping_method = value;
                        break;
                    case "X9":
                        entity.clamping_method = value;
                        break;
                    case "Z10":
                        entity.coordinate_name = value;
                        break;
                    case "X10":
                        entity.coordinate_name = value;
                        break;
                    case "Z11":
                        entity.z_max = value;
                        break;
                    case "X11":
                        entity.z_max = value;
                        break;
                    case "Z12":
                        entity.z_min = value;
                        break;
                    case "X12":
                        entity.z_min = value;
                        break;
                    case "Z14":
                        entity.processing_machines = value;
                        break;
                    case "X14":
                        entity.processing_machines = value;
                        break;
                    case "Z15":
                        entity.program = value;
                        break;
                    case "X15":
                        entity.program = value;
                        break;
                    case "Z16":
                        entity.processing_time = value;
                        break;
                    case "X16":
                        entity.processing_time = value;
                        break;
                }
            }
            else if (cell.RowIndex == 16 || cell.RowIndex == 17)
            {

            }
            else
            {
                string pattern = @"^([A-Z]{1,2})(\d+)$"; // 正则表达式模式，使用捕获组
                Match match = Regex.Match(cell.Address.ToString(), pattern);
                if (match.Success)
                {
                    string firstLetter = match.Groups[1].Value; // 获取第一个字母的值
                    string digits = match.Groups[2].Value; // 获取数字的值

                    if (firstLetter == "A")
                    {
                        upper_ftpprograms.Add(new upper_ftpprogram() { sequence = value });
                    }
                    else
                    {
                        switch (firstLetter)
                        {
                            case "C":
                                upper_ftpprograms.LastOrDefault().program_name = value;
                                break;
                            case "F":
                                upper_ftpprograms.LastOrDefault().tool_collet = value;
                                break;

                            case "G":
                                upper_ftpprograms.LastOrDefault().tool_handle = value;
                                break;

                            case "H":

                                upper_ftpprograms.LastOrDefault().tool_name = value;
                                break;
                            case "K":
                                upper_ftpprograms.LastOrDefault().knife_length = decimal.TryParse(value, out var aa) ? aa : 0;
                               
                                break;
                            case "M":
                                upper_ftpprograms.LastOrDefault().blade_length = decimal.TryParse(value, out var bb) ? bb : 0;
                                break;
                            case "O":
                                upper_ftpprograms.LastOrDefault().category = value;
                                if (value == "光")
                                {
                                    upper_ftpprograms.LastOrDefault().accuracy = 0.03m;
                                }
                                else
                                {
                                    upper_ftpprograms.LastOrDefault().accuracy = 0.1m;
                                }
                                break;
                            case "Q":
                                upper_ftpprograms.LastOrDefault().rotate_speed = decimal.TryParse(value, out var cc) ? cc : 0;
                                break;
                            case "S":
                                upper_ftpprograms.LastOrDefault().feed = value;
                                break;
                            case "U":
                                upper_ftpprograms.LastOrDefault().side_bottom_margin = value;
                                break;
                            case "W":
                                upper_ftpprograms.LastOrDefault().z_depth = decimal.TryParse(value, out var dd) ? dd : 0;
                                break;
                            case "Y":
                                upper_ftpprograms.LastOrDefault().remark = value;
                                break;
                            case "AD":
                                upper_ftpprograms.LastOrDefault().time = value;
                                break;

                            
                        }
                    }

                }



            }

        }



        #endregion


        public void WriteExcelFile(string path)
        {
            // 创建工作簿和工作表
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");

            // 创建行和单元格，并写入数据
            IRow row1 = sheet.CreateRow(0);
            row1.CreateCell(0).SetCellValue("Hello");
            row1.CreateCell(1).SetCellValue("World!");

            // 保存文件
            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
        }
    }
}
